package com.bz.xtcx.manager.mapper.provider;

import com.bz.xtcx.manager.persist.conditions.ExchangeCondition;
import com.bz.xtcx.manager.utils.WzDateUtil;
import com.bz.xtcx.manager.utils.WzStrUtil;
import com.bz.xtcx.manager.vo.VoQueryExchanges;
import org.springframework.util.StringUtils;

import java.util.Date;
import java.util.Map;

public class CmsExchangesProvider {
    public String countByConditionProvider(ExchangeCondition ec) {
        StringBuilder sql = new StringBuilder("select count(DISTINCT id) ");
        sql.append("from cms_exchanges ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(ec.getExName())) {
            sql.append(" and title like '%" + ec.getExName() + "%' ");
        }
        if (null != ec.getCheckStatus()) {
            sql.append(" and check_status = " + ec.getCheckStatus() + " ");
        }
        if (null != ec.getReadable()) {
            sql.append(" and readable = " + ec.getReadable() + " ");
        }
        if (null != ec.getExStart()) {
            sql.append(" and ex_start <= '" + WzDateUtil.dt2Str(ec.getExStart(), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != ec.getExEnd()) {
            sql.append(" and ex_end >= '" + WzDateUtil.dt2Str(ec.getExEnd(), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != ec.getEnrollStart()) {
            sql.append(" and enroll_start <= '" + WzDateUtil.dt2Str(ec.getEnrollStart(), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != ec.getEnrollEnd()) {
            sql.append(" and enroll_end = '" + WzDateUtil.dt2Str(ec.getEnrollEnd(), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (WzStrUtil.isNotBlank(ec.getKeyStr())) {
            sql.append(" and ( ");
            sql.append(" title like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or subject like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or ex_addr like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or organizer_name like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or org_contacts like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or org_phone like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or org_email like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or org_addr like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or ex_content like '%" + ec.getKeyStr() + "%' ");
            sql.append(" ) ");
        }
        return sql.toString();
    }

    public String selectByConditionProvider(ExchangeCondition ec) {

        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.title title, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.cover cover, ");
        sql.append("a.ex_addr ex_addr, ");
        sql.append("a.ex_start ex_start, ");
        sql.append("a.ex_end ex_end, ");
        sql.append("a.enroll_start enroll_start, ");
        sql.append("a.enroll_end enroll_end, ");
        sql.append("a.organizer_name organizer_name, ");
        if (WzStrUtil.isBlank(ec.getLoginUserId())) {
            sql.append("1 allow_enroll, ");
        } else {
            sql.append("(select count(c.id) from cms_exchanges_enroll c where c.exchanges_id = a.id and c.user_id = '" + ec.getLoginUserId() + "') allow_enroll, ");
        }
        sql.append("a.org_contacts org_contacts, ");
        sql.append("a.org_phone org_phone, ");
        sql.append("a.ex_content ex_content, ");
        if (ec.isLoadAll()) {
            sql.append("a.org_email org_email, ");
            sql.append("a.org_addr org_addr, ");
            sql.append("a.publish_now publish_now, ");
            sql.append("a.publish_date publish_date, ");
            sql.append("a.publish_user_id publish_user_id, ");
            sql.append("a.check_date check_date, ");
            sql.append("a.check_user_id check_user_id, ");
            sql.append("a.stick_sort stick_sort, ");
            sql.append("a.check_status check_status, ");
            sql.append("a.readable readable, ");
            sql.append("a.creater creater, ");
            sql.append("a.create_time create_time, ");
            sql.append("a.updater updater, ");
            sql.append("a.update_time update_time, ");
            sql.append("b.file_name file_name, ");
            sql.append("b.save_path save_path, ");
            sql.append("b.req_rel_url req_rel_url, ");
            sql.append("b.id file_id, ");
        }
        sql.append(" g.info info,g.checker checker,g.check_time check_time, ");
        sql.append(" a.subject subject ");
        sql.append(" from cms_exchanges a ");
        if (ec.isLoadAll()) {
            sql.append(" left join cms_file b on b.owner_type = 1 and b.owner_id = a.id ");
        }
        sql.append(" left join (select * from `bus_user_form_check` x where check_time =(select max(check_time) from `bus_user_form_check` y where x.form_id = y.form_id)) g on a.id = g.form_id ");
        sql.append(" where 1 = 1 ");
        if (WzStrUtil.isNotBlank(ec.getExId())) {
            sql.append(" and a.id = '" + ec.getExId() + "' ");
        }
        if (WzStrUtil.isNotBlank(ec.getExName())) {
            sql.append(" and a.title like '%" + ec.getExName() + "%' ");
        }
        if (null != ec.getCheckStatus()) {
            sql.append(" and a.check_status = " + ec.getCheckStatus() + " ");
        }
        if (null != ec.getReadable()) {
            sql.append(" and a.readable = " + ec.getReadable() + " ");
        }
        if (null != ec.getExStart()) {
            sql.append(" and a.ex_start <= '" + WzDateUtil.dt2Str(ec.getExStart(), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != ec.getExEnd()) {
            sql.append(" and a.ex_end >= '" + WzDateUtil.dt2Str(ec.getExEnd(), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != ec.getEnrollStart()) {
            sql.append(" and a.enroll_start <= '" + WzDateUtil.dt2Str(ec.getEnrollStart(), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (null != ec.getEnrollEnd()) {
            sql.append(" and a.enroll_end = '" + WzDateUtil.dt2Str(ec.getEnrollEnd(), WzDateUtil.DT_FMT_5) + "' ");
        }
        if (WzStrUtil.isNotBlank(ec.getKeyStr())) {
            sql.append(" and ( ");
            sql.append(" a.title like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or a.subject like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or a.ex_addr like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or a.organizer_name like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or a.org_contacts like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or a.org_phone like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or a.org_email like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or a.org_addr like '%" + ec.getKeyStr() + "%' ");
            sql.append(" or a.ex_content like '%" + ec.getKeyStr() + "%' ");
            sql.append(" ) ");
        }
        sql.append(" and a.publish_date < '" + WzDateUtil.dt2Str(new Date(), WzDateUtil.DT_FMT_5) + "' ");
        sql.append(" order by a.stick_sort, a.update_time desc, a.publish_date desc, a.id asc ");
        if (ec.getNeedPaging()) {
            sql.append(" limit " + ec.getPageBegin() + ", " + ec.getPageSize());
        }
        return sql.toString();
    }

    public String selectByPrimaryKeyProvider(Map<String, String> param) {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.title title, ");
        sql.append("a.subject subject, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.cover cover, ");
        sql.append("a.ex_addr ex_addr, ");
        sql.append("a.ex_start ex_start, ");
        sql.append("a.ex_end ex_end, ");
        sql.append("a.enroll_start enroll_start, ");
        sql.append("a.enroll_end enroll_end, ");
        if (WzStrUtil.isBlank(param.get("userId"))) {
            sql.append("1 allow_enroll, ");
        } else {
            sql.append("(select count(c.id) from cms_exchanges_enroll c where c.exchanges_id = a.id and c.user_id = '" + param.get("userId") + "') allow_enroll, ");
        }
        sql.append("a.organizer_name organizer_name, ");
        sql.append("a.org_contacts org_contacts, ");
        sql.append("a.org_phone org_phone, ");
        sql.append("a.org_email org_email, ");
        sql.append("a.org_addr org_addr, ");
        sql.append("a.publish_now publish_now, ");
        sql.append("a.publish_date publish_date, ");
        sql.append("a.publish_user_id publish_user_id, ");
        sql.append("a.check_date check_date, ");
        sql.append("a.check_user_id check_user_id, ");
        sql.append("a.stick_sort stick_sort, ");
        sql.append("a.ex_content ex_content, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.readable readable, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("b.file_name file_name, ");
        sql.append("b.save_path save_path, ");
        sql.append("b.req_rel_url req_rel_url, ");
        sql.append("b.id file_id ");
        sql.append("from cms_exchanges a left join cms_file b on b.owner_type = 1 and b.owner_id = a.id ");
        sql.append("where 1 = 1 ");
        sql.append(" and a.id = '" + param.get("id") + "' ");
        return sql.toString();
    }
    
    public String findByCondition(VoQueryExchanges e) {
		StringBuilder sql = new StringBuilder("select a.id, a.title, a.subject, a.type_id typeId, a.enroll_start enrollStart, a.enroll_end enrollEnd, a.ex_start exStart, a.ex_end exEnd, ")
		.append("a.publish_date publishDate, a.check_date checkDate, a.stick_sort stickSort, a.check_status checkStatus, a.readable, a.creater, a.updater, a.create_time createTime, a.update_time updateTime, ")
		.append("u.user_name as publishUserId, v.user_name as checkUserId, (select count(*) from `cms_exchanges_enroll` b where b.exchanges_id = a.id) as enrollNumber, ")
        .append("g.info info,g.checker checker,g.check_time check_time ");
		
		sql.append(" from `cms_exchanges` a left join `sys_user` u on a.publish_user_id = u.user_id ")
		.append(" left join `sys_user` v on a.check_user_id = v.user_id ")
        .append(" left join (select * from `bus_user_form_check` x where check_time =(select max(check_time) from `bus_user_form_check` y where x.form_id = y.form_id)) g on a.id = g.form_id ");
		
		sql.append("where 1=1");
		sql.append(queryCondition(e));
        return sql.toString();
	}
	
	StringBuilder queryCondition(VoQueryExchanges e) {
		StringBuilder sql = new StringBuilder();
		if(e != null) {
			if (!StringUtils.isEmpty(e.getPublishUserId())) {
				sql.append(" and a.publish_user_id = '"+e.getPublishUserId()+"'");
			}
			if (!StringUtils.isEmpty(e.getCheckStatus())) {
				sql.append(" and a.check_status = '"+e.getCheckStatus()+"'");
			}
			if (!StringUtils.isEmpty(e.getExStart())) {
				sql.append(" and a.ex_start >= '"+e.getExStart()+"'");
			}
			if (!StringUtils.isEmpty(e.getExEnd())) {
				sql.append(" and a.ex_end <= '"+e.getExEnd()+"'");
			}
			if (!StringUtils.isEmpty(e.getPublishStart())) {
				sql.append(" and a.publish_date >= '"+e.getPublishStart()+"'");
			}
			if (!StringUtils.isEmpty(e.getPublishEnd())) {
				sql.append(" and a.publish_date <= '"+e.getPublishEnd()+"'");
			}
			if (!StringUtils.isEmpty(e.getObjName())) {
                sql.append(" and ( ");
                sql.append(" a.title like '%" + e.getObjName() + "%' ");
                sql.append(" or a.subject like '%" + e.getObjName() + "%' ");
                sql.append(" or a.ex_addr like '%" + e.getObjName() + "%' ");
                sql.append(" or a.organizer_name like '%" + e.getObjName() + "%' ");
                sql.append(" or a.org_contacts like '%" + e.getObjName() + "%' ");
                sql.append(" or a.org_phone like '%" + e.getObjName() + "%' ");
                sql.append(" or a.org_email like '%" + e.getObjName() + "%' ");
                sql.append(" or a.org_addr like '%" + e.getObjName() + "%' ");
                sql.append(" or a.ex_content like '%" + e.getObjName() + "%' ");
                sql.append(" ) ");
			}
		}
		return sql;
	}
}
